package com.feng.database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;

import com.feng.util.Page;

public class BaseDao implements Dao {

	public Collection query(String inSQL, Connection inConnection,
			String inErrorMessage) throws Exception {
		return query(inSQL, inConnection, inErrorMessage, inErrorMessage);
	}

	public Collection query(String inSQL, Connection inConnection,
			String inErrorMessage, String inClientErrorMessage)
			throws Exception {
		Statement statement = null;
		ResultSet rs = null;

		try {
			statement = inConnection.createStatement();

			rs = statement.executeQuery(inSQL);
			return getRecords(rs);
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (statement != null) {
					statement.close();
					statement = null;
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}

		}
	}

	public HashMap load(String inSQL, Connection inConnection,
			String inErrorMessage) throws Exception {
		return load(inSQL, inConnection, inErrorMessage, inErrorMessage);
	}

	public HashMap load(String inSQL, Connection inConnection,
			String inErrorMessage, String inClientErrorMessage)
			throws Exception {
		Statement statement = null;
		ResultSet rs = null;

		try {
			statement = inConnection.createStatement();
			rs = statement.executeQuery(inSQL);
			return getRecord(rs);
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (statement != null) {
					statement.close();
					statement = null;
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
	}

	public int find(String inSQL, Connection inConnection, String inErrorMessage)
			throws Exception {
		return find(inSQL, inConnection, inErrorMessage, inErrorMessage);
	}

	public int find(String inSQL, Connection inConnection,
			String inErrorMessage, String inClientErrorMessage)
			throws Exception {
		Statement statement = null;
		ResultSet rs = null;
		try {
			statement = inConnection.createStatement();
			rs = statement.executeQuery(inSQL);
			if (rs.next()) {
				return rs.getInt(1);
			}
			return -1;
		} finally {
			if (null != statement) {
				try {
					statement.close();
				} catch (SQLException ex) {
				}
				statement = null;
			}
			if (null != rs) {
				try {
					rs.close();
				} catch (Exception e) {
				}
				rs = null;
			}
		}
	}

	public HashMap getRecord(ResultSet rs) throws SQLException {
		ResultSetMetaData rsmeta = rs.getMetaData();

		HashMap recordHashMap = null;
		String colName;
		int colType;
		int i;
		int numOfCols = rsmeta.getColumnCount();
		if (rs.next()) {
			recordHashMap = new HashMap();

			for (i = 1; i <= numOfCols; i++) {
				colName = rsmeta.getColumnName(i);
				if (rs.getObject(colName) == null) {
					continue;
				}
				colType = rsmeta.getColumnType(i);
				if (Types.NUMERIC == rsmeta.getColumnType(i))
					recordHashMap.put(colName.toLowerCase(), chgStr(rs
							.getString(colName)));
				else if ((Types.BLOB == rsmeta.getColumnType(i) || Types.CLOB == rsmeta
						.getColumnType(i)))
					recordHashMap.put(colName.toLowerCase(), rs
							.getClob(colName) == null ? "" : rs
							.getClob(colName).getSubString(1,
									((int) rs.getClob(colName).length())));
				else {
					recordHashMap.put(colName.toLowerCase(), rs
							.getString(colName));
				}
			}
		}
		return recordHashMap;
	}

	public int execute(String inSQL, Connection inConnection,
			String inErrorMessage) throws Exception {
		return execute(inSQL, inConnection, inErrorMessage, inErrorMessage);
	}

	public int execute(String inSQL, Connection inConnection,
			String inErrorMessage, String inClientErrorMessage)
			throws Exception {
		Statement statement = null;
		try {
			statement = inConnection.createStatement();
			return statement.executeUpdate(inSQL);
		} finally {
			if (null != statement) {
				try {
					statement.close();
				} catch (SQLException ex) {
					ex.printStackTrace();
				}
			}
			statement = null;
		}
	}

	public Collection getRecords(ResultSet rs) throws SQLException {
		ArrayList rsList = new ArrayList();
		ResultSetMetaData rsmeta = rs.getMetaData();
		Map recordHashMap;
		String colName;
		String colValue;
		int colType;
		int i;
		int numOfCols = rsmeta.getColumnCount();
		while (rs.next()) {
			recordHashMap = new HashMap();
			for (i = 1; i <= numOfCols; i++) {
				colName = rsmeta.getColumnName(i);
				colType = rsmeta.getColumnType(i);
				if (rs.getObject(colName) == null) {
					continue;
				}
				colValue = rs.getString(colName);
				colName = colName.toLowerCase();
				if (Types.NUMERIC == rsmeta.getColumnType(i))
					recordHashMap.put(colName.toLowerCase(), chgStr(rs
							.getString(colName)));
				else if ((Types.BLOB == rsmeta.getColumnType(i) || Types.CLOB == rsmeta
						.getColumnType(i)))
					recordHashMap.put(colName.toLowerCase(), rs
							.getClob(colName) == null ? "" : rs
							.getClob(colName).getSubString(1,
									((int) rs.getClob(colName).length())));
				else
					recordHashMap.put(colName, colValue);
			}
			rsList.add(recordHashMap);
		}
		return rsList;
	}

	public boolean query(Connection conn, String inSQL, HashMap data) throws Exception {
		Statement statement = null;
		ResultSet rs = null;
		try {
			statement = conn.createStatement();
			rs = statement.executeQuery(inSQL);
			int _i_page = 1;
			int _i_size = 10;
			String _p_page = (String) data.get("_p_page");
			if (null != _p_page)
				_i_page = Integer.parseInt(_p_page);
			String _p_size = (String) data.get("_p_size");
			if (null != _p_size)
				_i_size = Integer.parseInt(_p_size);
			ArrayList rsList = new ArrayList();

			ResultSetMetaData rsmeta = rs.getMetaData();
			Map recordHashMap;
			String colName;
			String colValue;
			int colType;
			int i;
			int numOfCols = rsmeta.getColumnCount();
			rs.last();
			int num = rs.getRow();
			data.put("_p_total_count", num);
			data.put("_p_total_page", (int) Math.round(num / (double) _i_size
					+ 0.4));
			data.put("_p_page", _i_page + "");
			data.put("_p_size", _i_size + "");
			int beg = (_i_page - 1) * _i_size;
			if (beg == 0)
				rs.beforeFirst();
			else
				rs.absolute(beg);
			int cnt = 0;
			while (rs.next()) {
				recordHashMap = new HashMap();
				for (i = 1; i <= numOfCols; i++) {
					colName = rsmeta.getColumnName(i);
					colType = rsmeta.getColumnType(i);
					if (rs.getObject(colName) == null) {
						continue;
					}
					colValue = rs.getString(colName);
					colName = colName.toLowerCase();
					if (Types.NUMERIC == rsmeta.getColumnType(i))
						recordHashMap.put(colName.toLowerCase(), chgStr(rs
								.getString(colName)));
					else if ((Types.BLOB == rsmeta.getColumnType(i) || Types.CLOB == rsmeta
							.getColumnType(i)))
						recordHashMap.put(colName.toLowerCase(), rs
								.getClob(colName) == null ? "" : rs.getClob(
								colName).getSubString(1,
								((int) rs.getClob(colName).length())));
					else
						recordHashMap.put(colName, colValue);
				}
				rsList.add(recordHashMap);
				cnt++;
				if (cnt >= _i_size)
					break;
			}
			data.put("lists", rsList);
		} finally {
			if (null != statement) {
				try {
					statement.close();
				} catch (SQLException ex) {
				}
				statement = null;
			}
			if (null != rs) {
				try {
					rs.close();
				} catch (Exception e) {
				}
				rs = null;
			}
		}
		return true;
	}
	public Page query(Connection conn, String inSQL,Page page) throws Exception {
		Statement statement = null;
		ResultSet rs = null;
		try {
			statement = conn.createStatement();
			rs = statement.executeQuery(inSQL);
			ArrayList rsList = new ArrayList();

			ResultSetMetaData rsmeta = rs.getMetaData();
			Map recordHashMap;
			String colName;
			String colValue;
			int colType;
			int i;
			int numOfCols = rsmeta.getColumnCount();
			rs.last();
			int num = rs.getRow();
			page.setAllCount(num);
			
			if (page.getBegin()-1 == 0)
				rs.beforeFirst();
			else
				rs.absolute(page.getBegin()-1);
			int cnt = 0;
			while (rs.next()) {
				recordHashMap = new HashMap();
				for (i = 1; i <= numOfCols; i++) {
					colName = rsmeta.getColumnName(i);
					colType = rsmeta.getColumnType(i);
					if (rs.getObject(colName) == null) {
						continue;
					}
					colValue = rs.getString(colName);
					colName = colName.toLowerCase();
					if (Types.NUMERIC == rsmeta.getColumnType(i))
						recordHashMap.put(colName.toLowerCase(), chgStr(rs.getString(colName)));
					else if ((Types.BLOB == rsmeta.getColumnType(i) || Types.CLOB == rsmeta.getColumnType(i)))
						recordHashMap.put(colName.toLowerCase(), rs.getClob(colName) == null ? "" : rs.getClob(colName).getSubString(1,
								((int) rs.getClob(colName).length())));
					else
						recordHashMap.put(colName, colValue);
				}
				rsList.add(recordHashMap);
				cnt++;
				if (cnt >= page.getPageSize())
					break;
			}
			page.setDataList(rsList);
		} finally {
			if (null != statement) {
				try {
					statement.close();
				} catch (SQLException ex) {
				}
				statement = null;
			}
			if (null != rs) {
				try {
					rs.close();
				} catch (Exception e) {
				}
				rs = null;
			}
		}
		return page;
	}
	String chgStr(String s) {
		if (s.indexOf(".") == 0
				|| (s.indexOf(".") == 1 && (s.charAt(0) == '-' || s.charAt(0) == '+')))
			return new StringBuffer(s).insert(s.indexOf("."), '0').toString();
		else
			return s;
	}

	public boolean isEmpty(Object s) {
		if (s == null || "".equals(s.toString().trim()))
			return true;
		else
			return false;
	}

	public String Uset(Map map, String key, int n) {
		if (n == Number) {
			if (isEmpty(map.get(key)))
				return key + "=0";
			else
				return key + "=" + map.get(key).toString();
		} else if (n == Varchar) {
			if (isEmpty(map.get(key)))
				return key + "=''";
			else
				return key + "='" + map.get(key).toString() + "'";
		} else
			return null;
	}

	public String Ival(Map map, String key, int n) {
		if (n == Number) {
			if (isEmpty(map.get(key)))
				return "0";
			else
				return map.get(key).toString();
		} else if (n == Varchar) {
			if (isEmpty(map.get(key)))
				return "''";
			else
				return "'" + map.get(key).toString() + "'";
		} else
			return null;
	}

	public String Ival(Map map, String key, int n, String def) {
		if (n == Number) {
			if (isEmpty(map.get(key)))
				return "0";
			else
				return map.get(key).toString();
		} else if (n == Varchar) {
			if (isEmpty(map.get(key)))
				return "'" + def + "'";
			else
				return "'" + map.get(key).toString() + "'";
		} else
			return null;
	}

	public void Debug(String s) {
		// if(null==ConstantValue.getDebug()||"1".equals(ConstantValue.getDebug()))
		// System.out.println(s);
	}

	public void closeConnection(Connection conn) {
		try {
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void testMsql(){
		Connection conn = null;
		try {
			  Class.forName("org.gjt.mm.mysql.Driver"); 
			  conn = java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/test?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=utf-8","root", "aaaaaa"); 
			  StringBuffer sb=new StringBuffer();
			  sb.append("select * from app");
			  HashMap map=new HashMap(); 
			  map.put("_p_page", "1");
			  map.put("_p_size", "3");
			  new BaseDao().query(conn,sb.toString(), map);
			ArrayList list = (ArrayList) map.get("lists");
			int size = list.size();
			for (int i = 0; i < size; i++) {
				Map aaa = (Map) list.get(i);
				System.out.println(aaa.get("name").toString());
			}
			int d = (int) Math.round(99 / (double) 10 + 0.4);
			System.out.print(d);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}
	
	public static void testMsql2(){
		Connection conn = null;
		try {
			  Class.forName("org.gjt.mm.mysql.Driver"); 
			  conn = java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/test?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=utf-8","root", "aaaaaa"); 
			  StringBuffer sb=new StringBuffer();
			  sb.append("select * from app order by id asc");
			  Page page = new Page(1, 91);
			  new BaseDao().query(conn,sb.toString(), page);
			ArrayList list = (ArrayList) page.getDataList();
			int size = list.size();
			for (int i = 0; i < size; i++) {
				Map aaa = (Map) list.get(i);
				System.out.println(aaa.get("name").toString());
			}
			int d = (int) Math.round(99 / (double) 10 + 0.4);
			System.out.print(d);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}
	
	public static void test(){
		Connection conn = null;
		try {
			Class.forName("org.apache.derby.jdbc.ClientDriver");
			conn = java.sql.DriverManager.getConnection("jdbc:derby://localhost:1527/myeclipse","classiccars", "myeclipse");
			StringBuffer sb = new StringBuffer();
			sb.append("select * from customer");
			Page page = new Page(20, 1);
			new BaseDao().query(conn, sb.toString(), page);
			ArrayList list = (ArrayList) page.getDataList();
			int size = list.size();
			for (int i = 0; i < size; i++) {
				Map aaa = (Map) list.get(i);
				System.out.println(aaa.get("lcaid").toString());
			}
			int d = (int) Math.round(99 / (double) 10 + 0.4);
			System.out.print(d);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}
	public static void test2(){
		Connection conn = null;
		try {
			
			Class.forName("org.apache.derby.jdbc.ClientDriver");
			conn = java.sql.DriverManager.getConnection("jdbc:derby://localhost:1527/myeclipse","classiccars", "myeclipse"); 
			  StringBuffer sb=new StringBuffer();
			  sb.append("select CUSTOMERNAME from customer");
			  HashMap map=new HashMap(); 
			  map.put("_p_page", "2");
			  map.put("_p_size", "10");
			  new BaseDao().query(conn,sb.toString(), map);
			  ArrayList list=(ArrayList)map.get("lists");
			  int size=list.size(); for(int i=0;i<size;i++) { Map
			  aaa=(Map)list.get(i);
			  System.out.println(aaa.get("lcaid").toString()); }
			 
			int d = (int) Math.round(99 / (double) 10 + 0.4);
			System.out.print(d);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
	}
	public static void main(String[] args) {
		//testMsql();
		testMsql2();
		//test();
	}
}
